/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package uit.ieproject.dataaccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import uit.ieproject.entities.Regex;
import uit.ieproject.utilites.DbConnection;
import uit.ieproject.utilites.Info;

/**
 *
 * @author Maivia
 */
public class RegexDAO {

    public ArrayList<Regex> getAllRegexs() throws SQLException {
        ArrayList<Regex> regexs = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        Statement stmt = con.createStatement();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM ");
        sql.append(Info.TABLE_REGEX);
        sql.append(" ORDER BY name");
        ResultSet rs = stmt.executeQuery(sql.toString());
        while (rs.next()) {
            Regex regex = new Regex();
            regex.setId(rs.getLong("id"));
            regex.setName(rs.getNString("name"));
            regex.setExpression(rs.getNString("expression"));
            regex.setType(rs.getNString("type"));
            regex.setIsActive(rs.getInt("isActive"));
            regexs.add(regex);
        }
        stmt.close();
        rs.close();
        con.close();
        return regexs;
    }

    public void addRegex(Regex regex) throws SQLException {
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO ");
        sql.append(Info.TABLE_REGEX);
        sql.append(" (name, expression, type, isActive) VALUES (?,?,?,?)");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, regex.getName());
        stmt.setString(2, regex.getExpression());
        stmt.setString(3, regex.getType());
        stmt.setInt(4, regex.getIsActive());
        stmt.executeUpdate();
        stmt.close();
        con.close();
    }

    public void deleteRegex(long[] id) throws SQLException {
        Connection con = DbConnection.getConnection();
        con.setAutoCommit(false);
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ");
        sql.append(Info.TABLE_REGEX);
        sql.append(" WHERE id=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        int size = id.length;
        for (int i = 0; i < size; i++) {
            stmt.setLong(1, id[i]);
            stmt.addBatch();
        }
        stmt.executeBatch();
        con.commit();
        stmt.close();
        con.close();
    }

    public void editRegex(Regex regex) throws SQLException {
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE ");
        sql.append(Info.TABLE_REGEX);
        sql.append(" SET name=?,type=?,expression=?,isActive=? WHERE id=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        stmt.setString(1, regex.getName());
        stmt.setString(2, regex.getType());
        stmt.setString(3, regex.getExpression());
        stmt.setInt(4, regex.getIsActive());
        stmt.setLong(5, regex.getId());
        stmt.executeUpdate();
        stmt.close();
        con.close();
    }

    public ArrayList<Regex> getRegex(String name, String type, int status) throws SQLException {
        ArrayList<Regex> regexs = new ArrayList<>();
        Connection con = DbConnection.getConnection();
        Statement stmt = con.createStatement();
        boolean flag = false;
        String sql = "SELECT * FROM Regex";
        if (!name.contains("Tất cả") || !type.contains("Tất cả") || status != 2) {
            sql += " WHERE ";
            if (!name.contains("Tất cả")) {
                sql += "name LIKE  '%'+N'" + name + "'+'%'";
                flag = true;
            }
            if (!type.contains("Tất cả")) {
                if (flag == true) {
                    sql += " AND ";
                }
                sql += "type LIKE '%'+N'" + type + "'+'%'";
                flag = true;
            }
            if (status != 2) {
                if (flag == true) {
                    sql += " AND ";
                }
                sql += "isActive='" + status + "'";
            }
        }
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            Regex regex = new Regex();
            regex.setId(rs.getLong("id"));
            regex.setName(rs.getNString("name"));
            regex.setExpression(rs.getNString("expression"));
            regex.setType(rs.getNString("type"));
            regex.setIsActive(rs.getInt("isActive"));
            regexs.add(regex);
        }
        rs.close();
        stmt.close();
        con.close();
        return regexs;
    }

    public String getAnswer(String person, String info) throws SQLException {
        String answer = null;
        if (info.equals("Năm sinh")) {
            info = "birthday";
        }
        Connection con = DbConnection.getConnection();
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT ");
        sql.append(info);
        sql.append(" FROM ");
        sql.append(Info.TABLE_PERSON);
        sql.append(" WHERE name=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        //stmt.setString(1, info);
        stmt.setString(1, person);
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            answer = rs.getNString(1);
        }
        rs.close();
        stmt.close();
        con.close();
        return answer;
    }

    public void activeRegexs(long[] ids, int isActive) throws SQLException {
        Connection con = DbConnection.getConnection();
        con.setAutoCommit(false);
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE ");
        sql.append(Info.TABLE_REGEX);
        sql.append(" SET isActive=? WHERE id=?");
        PreparedStatement stmt = con.prepareStatement(sql.toString());
        int length = ids.length;
        for (int i = 0; i < length; i++) {
            stmt.setInt(1, isActive);
            stmt.setLong(2, ids[i]);
            stmt.addBatch();
        }
        stmt.executeBatch();
        con.commit();
        stmt.close();
        con.close();
    }
}
